Increasing Operational Efficiency and Profits Through Coffee Shop Sales Analysis Using SQL¶

About Dataset¶

The dataset covers sales activities within a local coffee shop, encompassing a range of information such as transaction records and product details. By analyzing this data using SQL queries and data analysis techniques, we aim to uncover valuable insights that can inform decision-making and drive business growth.

1. Overview of the Dataset¶

The dataset is divided into three tables, with each column labeled according to its function. The following sections provide an overview of each table's structure as well as an entity-relationship diagram of the three tables. To enhance visualization, we using Python's Pandas package.

In [2]:
import pandas as pd
import psycopg2 as ps
import matplotlib.pyplot as plt
In [3]:
#Import dataset
coffeshope_transaction = pd.read_csv('coffeshope_transaction.csv')
coffeshope_product = pd.read_csv('coffeshope_product.csv')
coffeshope_timedetail = pd.read_csv('coffeshope_timedetail.csv')
In [27]:
#Basic information dataset coffeshope_transaction
coffeshope_transaction.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 149116 entries, 0 to 149115
Data columns (total 6 columns):
 #   Column            Non-Null Count   Dtype 
---  ------            --------------   ----- 
 0   transaction_id    149116 non-null  int64 
 1   transaction_date  149116 non-null  object
 2   transaction_time  149116 non-null  object
 3   store_id          149116 non-null  int64 
 4   store_location    149116 non-null  object
 5   product_id        149116 non-null  int64 
dtypes: int64(3), object(3)
memory usage: 6.8+ MB
In [28]:
#Structure information dataset coffeshope_transaction
coffeshope_transaction.head()
Out[28]:
transaction_id transaction_date transaction_time store_id store_location product_id
0 114301 01-06-2023 11:33:29 3 Astoria 45
1 115405 02-06-2023 11:18:24 3 Astoria 45
2 115478 02-06-2023 12:02:45 3 Astoria 45
3 116288 02-06-2023 19:39:47 3 Astoria 45
4 116714 03-06-2023 12:24:57 3 Astoria 45
In [29]:
#Basic information dataset coffeshope_product
coffeshope_product.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 149116 entries, 0 to 149115
Data columns (total 7 columns):
 #   Column            Non-Null Count   Dtype  
---  ------            --------------   -----  
 0   transaction_id    149116 non-null  int64  
 1   transaction_qty   149116 non-null  int64  
 2   unit_price        149116 non-null  float64
 3   Total_Bill        149116 non-null  float64
 4   product_category  149116 non-null  object 
 5   product_type      149116 non-null  object 
 6   product_detail    149116 non-null  object 
dtypes: float64(2), int64(2), object(3)
memory usage: 8.0+ MB
In [30]:
#Structure information dataset coffeshope_product
coffeshope_product.head()
Out[30]:
transaction_id transaction_qty unit_price Total_Bill product_category product_type product_detail
0 114301 1 3.0 3.0 Tea Brewed herbal tea Peppermint
1 115405 1 3.0 3.0 Tea Brewed herbal tea Peppermint
2 115478 1 3.0 3.0 Tea Brewed herbal tea Peppermint
3 116288 1 3.0 3.0 Tea Brewed herbal tea Peppermint
4 116714 1 3.0 3.0 Tea Brewed herbal tea Peppermint
In [31]:
#Basic information dataset coffeshope_timedetail
coffeshope_timedetail.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 149116 entries, 0 to 149115
Data columns (total 6 columns):
 #   Column          Non-Null Count   Dtype 
---  ------          --------------   ----- 
 0   transaction_id  149116 non-null  int64 
 1   Month Name      149116 non-null  object
 2   Day Name        149116 non-null  object
 3   Hour            149116 non-null  int64 
 4   Month           149116 non-null  int64 
 5   Day of Week     149116 non-null  int64 
dtypes: int64(4), object(2)
memory usage: 6.8+ MB
In [32]:
#Structure information dataset coffeshope_timedetail
coffeshope_timedetail.head()
Out[32]:
transaction_id Month Name Day Name Hour Month Day of Week
0 114301 June Thursday 11 6 3
1 115405 June Friday 11 6 4
2 115478 June Friday 12 6 4
3 116288 June Friday 19 6 4
4 116714 June Saturday 12 6 5

Trends and Patterns¶

In [4]:
#Connect postgresql to jupyter
pga_coffeshop = ps.connect(dbname='coffeshop',
                           user='postgres',
                           password='Rider479',
                           host='localhost',
                           port='5432')

1. Menu item trends¶

In [145]:
#Popular drink item on the menu
query_drinkitem = """SELECT product_detail, SUM(CAST(total_bill AS NUMERIC)) AS total_profit
FROM coffeshop_product
WHERE product_category LIKE '%Coffee%' 
OR product_category LIKE '%Tea%'
OR product_category LIKE '%Drink%'
OR product_category LIKE '%Flavours'
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [146]:
#Read sql code for drink item filter
drink_trends = pd.read_sql_query(query_drinkitem, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\2886001651.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  drink_trends = pd.read_sql_query(query_drinkitem, pga_coffeshop)
In [147]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(drink_trends['product_detail'], drink_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Sales by Drink Item', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Profit', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [142]:
#Popular food item on the menu
query_fooditem = """SELECT product_detail, SUM(CAST(total_bill AS NUMERIC)) AS total_profit
FROM coffeshop_product
WHERE product_category LIKE '%Bakery%' 
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [143]:
#Read sql code for food item filter
food_trends = pd.read_sql_query(query_fooditem, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\622470206.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  food_trends = pd.read_sql_query(query_fooditem, pga_coffeshop)
In [144]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(food_trends['product_detail'], food_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Sales by Item', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image

2. Timeline trends¶

a. Trends by month¶

In [5]:
#Popular item menu in january
query_january = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE month_name IN ('January')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [47]:
#Read sql code for january filter
january_trends = pd.read_sql_query(query_january, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\3770423570.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  january_trends = pd.read_sql_query(query_january, pga_coffeshop)
In [48]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(january_trends['product_detail'], january_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('January Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [49]:
#Popular item menu in february
query_february = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE month_name IN ('February')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [50]:
#Read sql code for february filter
february_trends = pd.read_sql_query(query_february, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\687763761.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  february_trends = pd.read_sql_query(query_february, pga_coffeshop)
In [51]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(february_trends['product_detail'], february_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('February Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [52]:
#Popular item menu in march
query_march = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE month_name IN ('January')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [53]:
#Read sql code for march filter
march_trends = pd.read_sql_query(query_march, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\3877534211.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  march_trends = pd.read_sql_query(query_march, pga_coffeshop)
In [55]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(march_trends['product_detail'], march_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('March Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [56]:
#Popular item menu in april
query_april = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE month_name IN ('April')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [57]:
#Read sql code for april filter
april_trends = pd.read_sql_query(query_april, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\31636772.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  april_trends = pd.read_sql_query(query_april, pga_coffeshop)
In [58]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(april_trends['product_detail'], april_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('April Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [59]:
#Popular item menu in may
query_may = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE month_name IN ('May')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [60]:
#Read sql code for may filter
may_trends = pd.read_sql_query(query_may, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\1037075910.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  may_trends = pd.read_sql_query(query_may, pga_coffeshop)
In [61]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(may_trends['product_detail'], may_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('May Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [62]:
#Popular item menu in june
query_june = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE month_name IN ('June')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [63]:
#Read sql code for june filter
june_trends = pd.read_sql_query(query_june, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\3148631641.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  june_trends = pd.read_sql_query(query_june, pga_coffeshop)
In [64]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(june_trends['product_detail'], june_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('June Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image

b. Trends by day¶

In [6]:
#Popular item menu in monday
query_monday = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE day_name IN ('Monday')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [8]:
#Read sql code for monday filter
monday_trends = pd.read_sql_query(query_monday, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_21392\3075462421.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  monday_trends = pd.read_sql_query(query_monday, pga_coffeshop)
In [67]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(monday_trends['product_detail'], monday_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Monday Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [68]:
#Popular item menu in tuesday
query_tuesday = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE day_name IN ('Tuesday')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [69]:
#Read sql code for tuesday filter
tuesday_trends = pd.read_sql_query(query_tuesday, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\1743690467.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  tuesday_trends = pd.read_sql_query(query_tuesday, pga_coffeshop)
In [70]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(tuesday_trends['product_detail'], tuesday_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Tuesday Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [71]:
#Popular item menu in wednesday
query_wednesday = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE day_name IN ('Wednesday')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [72]:
#Read sql code for wednesday filter
wednesday_trends = pd.read_sql_query(query_wednesday, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\3304511906.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  wednesday_trends = pd.read_sql_query(query_wednesday, pga_coffeshop)
In [73]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(wednesday_trends['product_detail'], wednesday_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Wednesday Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [74]:
#Popular item menu in thursday
query_thursday = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE day_name IN ('Thursday')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [75]:
#Read sql code for thursday filter
thursday_trends = pd.read_sql_query(query_thursday, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\1645306937.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  thursday_trends = pd.read_sql_query(query_thursday, pga_coffeshop)
In [76]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(thursday_trends['product_detail'], thursday_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Thursday Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [77]:
#Popular item menu in friday
query_friday = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE day_name IN ('Friday')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [78]:
#Read sql code for friday filter
friday_trends = pd.read_sql_query(query_friday, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\389561128.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  friday_trends = pd.read_sql_query(query_friday, pga_coffeshop)
In [79]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(friday_trends['product_detail'], friday_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Friday Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [80]:
#Popular item menu in saturday
query_saturday = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE day_name IN ('Saturday')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [81]:
#Read sql code for saturday filter
saturday_trends = pd.read_sql_query(query_saturday, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\2001153849.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  saturday_trends = pd.read_sql_query(query_saturday, pga_coffeshop)
In [82]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(saturday_trends['product_detail'], saturday_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Saturday Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [83]:
#Popular item menu in sunday
query_sunday = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE day_name IN ('Sunday')
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [84]:
#Read sql code for sunday filter
sunday_trends = pd.read_sql_query(query_sunday, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\658630782.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  sunday_trends = pd.read_sql_query(query_sunday, pga_coffeshop)
In [85]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(sunday_trends['product_detail'], sunday_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Sunday Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image

c. Trends by time¶

In [86]:
#Popular item menu in 6
query_six = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (6)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [87]:
#Read sql code for six filter
six_trends = pd.read_sql_query(query_six, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\2191370885.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  six_trends = pd.read_sql_query(query_six, pga_coffeshop)
In [88]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(six_trends['product_detail'], six_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Six Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [89]:
#Popular item menu in 7
query_seven = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (7)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [90]:
#Read sql code for seven filter
seven_trends = pd.read_sql_query(query_seven, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\3650597955.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  seven_trends = pd.read_sql_query(query_seven, pga_coffeshop)
In [91]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(seven_trends['product_detail'], seven_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Seven Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [92]:
#Popular item menu in 8
query_eight = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (8)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [93]:
#Read sql code for eight filter
eight_trends = pd.read_sql_query(query_eight, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\655862744.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  eight_trends = pd.read_sql_query(query_eight, pga_coffeshop)
In [94]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(eight_trends['product_detail'], eight_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Eight Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [96]:
#Popular item menu in 9
query_nine = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (9)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [97]:
#Read sql code for nine filter
nine_trends = pd.read_sql_query(query_nine, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\1986868068.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  nine_trends = pd.read_sql_query(query_nine, pga_coffeshop)
In [98]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(nine_trends['product_detail'], nine_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Nine Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [99]:
#Popular item menu in 10
query_ten = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (10)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [100]:
#Read sql code for ten filter
ten_trends = pd.read_sql_query(query_ten, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\2862057697.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  ten_trends = pd.read_sql_query(query_ten, pga_coffeshop)
In [101]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(ten_trends['product_detail'], ten_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Ten Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [102]:
#Popular item menu in 11
query_eleven = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (11)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [103]:
#Read sql code for eleven filter
eleven_trends = pd.read_sql_query(query_eleven, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\1403211684.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  eleven_trends = pd.read_sql_query(query_eleven, pga_coffeshop)
In [104]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(eleven_trends['product_detail'], eleven_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Eleven Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [105]:
#Popular item menu in 12
query_twelve = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (12)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [106]:
#Read sql code for twelve filter
twelve_trends = pd.read_sql_query(query_twelve, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\2929766325.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  twelve_trends = pd.read_sql_query(query_twelve, pga_coffeshop)
In [107]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(twelve_trends['product_detail'], twelve_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Twelve Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [108]:
#Popular item menu in 13
query_thirteen = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (13)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [109]:
#Read sql code for thirteen filter
thirteen_trends = pd.read_sql_query(query_thirteen, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\3987381113.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  thirteen_trends = pd.read_sql_query(query_thirteen, pga_coffeshop)
In [110]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(thirteen_trends['product_detail'], thirteen_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Thirteen Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [111]:
#Popular item menu in 14
query_fourteen = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (14)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [112]:
#Read sql code for fourteen filter
fourteen_trends = pd.read_sql_query(query_fourteen, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\135007755.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  fourteen_trends = pd.read_sql_query(query_fourteen, pga_coffeshop)
In [113]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(fourteen_trends['product_detail'], fourteen_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Fourteen Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [114]:
#Popular item menu in 15
query_fiveteen = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (15)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [115]:
#Read sql code for fiveteen filter
fiveteen_trends = pd.read_sql_query(query_fiveteen, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\514787999.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  fiveteen_trends = pd.read_sql_query(query_fiveteen, pga_coffeshop)
In [116]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(fiveteen_trends['product_detail'], fiveteen_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Fiveteen Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [117]:
#Popular item menu in 16
query_sixteen = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (16)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [118]:
#Read sql code for sixteen filter
sixteen_trends = pd.read_sql_query(query_sixteen, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\3027236203.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  sixteen_trends = pd.read_sql_query(query_sixteen, pga_coffeshop)
In [119]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(sixteen_trends['product_detail'], sixteen_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Sixteen Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [120]:
#Popular item menu in 17
query_seventeen = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (17)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [121]:
#Read sql code for seventeen filter
seventeen_trends = pd.read_sql_query(query_seventeen, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\1140548598.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  seventeen_trends = pd.read_sql_query(query_seventeen, pga_coffeshop)
In [122]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(seventeen_trends['product_detail'], seventeen_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Seventeen Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [123]:
#Popular item menu in 18
query_eighteen = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (18)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [124]:
#Read sql code for january filter
eighteen_trends = pd.read_sql_query(query_eighteen, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\1526803039.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  eighteen_trends = pd.read_sql_query(query_eighteen, pga_coffeshop)
In [125]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(eighteen_trends['product_detail'], eighteen_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Eighteen Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [126]:
#Popular item menu in 19
query_nineteen = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (19)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [127]:
#Read sql code for nineteen filter
nineteen_trends = pd.read_sql_query(query_nineteen, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\1054117104.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  nineteen_trends = pd.read_sql_query(query_nineteen, pga_coffeshop)
In [128]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(nineteen_trends['product_detail'], nineteen_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Nineteen Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image
In [129]:
#Popular item menu in 20
query_twenty = """SELECT product_detail, SUM (total_bill) AS total_profit
FROM coffeshop_product
JOIN coffeshope_timedetail
ON coffeshop_product.product_trans_id = coffeshope_timedetail.timedetail_id
WHERE hour IN (20)
GROUP BY product_detail
ORDER BY total_profit DESC;"""
In [130]:
#Read sql code for twenty filter
twenty_trends = pd.read_sql_query(query_twenty, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\3611393923.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  twenty_trends = pd.read_sql_query(query_twenty, pga_coffeshop)
In [131]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(twenty_trends['product_detail'], twenty_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Twenty Item Sales', fontsize=16)
plt.xlabel('Menu Item', fontsize=14)
plt.ylabel('Total Sale', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image

3. Store trends¶

In [136]:
#Popular store
query_store = """SELECT store_location, SUM(CAST(total_bill AS NUMERIC)) AS total_profit
FROM coffeshop_product
JOIN coffeshop_transaction ON coffeshop_product.product_trans_id = coffeshop_transaction.transaction_id
WHERE store_location IN ('Astoria', 'Hell''s Kitchen', 'Lower Manhattan')
GROUP BY store_location
ORDER BY total_profit DESC;"""
In [137]:
#Read sql code for store filter
store_trends = pd.read_sql_query(query_store, pga_coffeshop)

C:\Users\User\AppData\Local\Temp\ipykernel_18476\2577334610.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  store_trends = pd.read_sql_query(query_store, pga_coffeshop)
In [138]:
# Set a figure size
plt.figure(figsize=(12, 8))

# Plot the bar chart
plt.bar(store_trends['store_location'], store_trends['total_profit'], color='blue')

# Add title and labels with font sizes
plt.title('Popular Store', fontsize=16)
plt.xlabel('Store', fontsize=14)
plt.ylabel('Trend Store', fontsize=14)

# Rotate x-axis labels
plt.xticks(rotation=90, fontsize=12)

# Increase tick label font size on y-axis
plt.yticks(fontsize=12)

# Show the plot
plt.show()
No description has been provided for this image

Analysis¶

Berdasarkan data tersebut, kita dapat menentukan beberapa kebiasaan pelanggan yang signifikan:

  1. Preferensi Minuman: Pelanggan cenderung lebih memilih minuman dari kategori Coffee, khususnya varian Ethiopia, yang menjadi favorit dan menghasilkan profit tertinggi.

  1. Pilihan Makanan: Chocolate Croissant menjadi pilihan utama pelanggan untuk makanan, menunjukkan bahwa makanan ringan ini memiliki popularitas yang tinggi dan dapat meningkatkan profit secara signifikan.

    Hal ini dapat dilihat bahwa trend Croissant pada media sosial yang diikuti oleh promosi yang dilakukan pada media sosial dapat mendorong trend penjualan

  1. Pola Pembelian Bulanan: Terdapat peningkatan profit pada bulan Juni untuk menu minuman, mungkin karena faktor musiman atau promosi yang terkait dengan bulan tersebut.

    Peningkatan profit pada bulan Juni dalam data penjualan disebabkan oleh musim liburan musim panas yang sering terjadi pada bulan tersebut.

  1. Preferensi Hari dan Jam: Pelanggan cenderung melakukan pembelian pada hari Senin dan pada jam 10 pagi. Hal ini mungkin dipengaruhi oleh jadwal atau rutinitas pelanggan, di mana mereka lebih cenderung untuk membeli pada awal minggu dan pada jam makan siang.

Conculsion¶

In this data analysis project, we set out on a journey to extract valuable insights from Coffee Shop sales data. We started by preparing and exploring the dataset, cleaning up any missing or null values, and developing new features to help us find meaningful patterns.

We answered a wide range of questions, from understanding product performance and sales trends to delving into customer behavior. These findings are critical for Coffee Shop sales strategies and can inform future optimizations.